var express = require('express')
var router = express.Router()
const { getAll, getById, exec } = require('../db/index')

// 解构调试的的标记,将来会根据这个决定是否发送错误信息
const { debug } = require('../config/index')

// 获取所有的分类
router.get('/', async function (req, res) {
  // 操作数据库三部曲
  //1,获取请求的参数
  //2,编写sql语句
  //3,执行sql语句,返回结果
  // let sql = 'select * from category'
  let sql = `select 
              t2.sum as value,t1.tag as name
              from 
	            category t1,(SELECT cate_id,COUNT(cate_id) sum FROM articles GROUP BY cate_id) t2
              where
	            t1.id=t2.cate_id `

  try {
    const data = await getAll(sql)
    res.send({
      code: 0,
      message: '获取所有分类成功',
      result: data,
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '获取所有分类失败',
      result: debug ? error : '',
    })
  }
})

// 获取每个月份下的文章产量
router.get('/output', async function (req, res) {
  let sql = `SELECT 
              MONTH(created_time) as month,count(*) as count 
              from articles
              GROUP BY MONTH(created_time)`

  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '查询成功',
    result: data,
  })
})

// 获取所有文章
router.get('/allpost', async (req, res) => {
  // 解析请求参数
  const { page = 1, size = 5 } = req.query
  let sql = `select count(*) as total from articles`
  const { total } = await getById(sql)
  // 偏移量=(page-1)*size
  let offset = (page - 1) * size

  let sql1 = `SELECT articles.*,category.tag FROM articles INNER JOIN category ON articles.cate_id = category.id ORDER BY articles.id  ASC
              limit ${offset},${size} `

  try {
    const data = await getAll(sql1)
    // console.log(data) //返回的是一个数组,
    res.send({
      code: 0,
      message: '获取所有文章成功',
      result: { total, data },
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '获取所有文章失败',
      result: debug ? error : '',
    })
  }
})

//删除选中文文章

router.delete('/:id', async function (req, res) {
  //1,获取请求的参数
  const id = req.params.id
  //2,编写sql语句
  let sql = `delete from articles where id=${id}`
  //执行sql语句,返回结果
  try {
    const data = await exec(sql)
    res.send({
      code: 0,
      message: '删除文章成功',
      result: '',
    })
  } catch (error) {
    res.send({
      code: 100108,
      message: '删除文章失败',
      result: debug ? error : '',
    })
  }
})

//搜索实现
router.patch('/', async (req, res) => {
  // 解析请求参数
  const { keywords, page = 1, size = 5 } = req.query
  // return
  // console.log(req.query)
  const offset = (page - 1) * size

  // 模糊查询  是否加引号,传入的时候也要注意是否加了引号
  let sql = `SELECT articles.*,category.tag FROM articles INNER JOIN category ON articles.cate_id = category.id where keywords like '%${keywords}%' ORDER BY articles.id  ASC limit ${offset},${size} `
  let sql1 = `select count(*) as total from articles where keywords like '%${keywords}%' `

  console.log(sql)
  const data = await exec(sql)
  const total = await getById(sql1)
  console.log(data)
  console.log(total)

  try {
    // const data = await exec(sql)
    // console.log(data) //返回的是一个数组,
    res.send({
      code: 0,
      message: '搜索成功',
      result: { total, data },
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '搜索失败',
      result: debug ? error : '',
    })
  }
})

module.exports = router
